/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package quanlinganhang.dao;

import java.io.*;
import java.sql.*;
import java.util.*;
import javax.swing.table.DefaultTableModel;

/**
 * Class kết nối csdl và thực hiện các truy vấn
 *
 * @version 1.0 18/4/2013
 * @author phamduong
 */
public class SqlDAOHelper {

    protected static String sUsername;
    protected static String sPassword;
    protected static String sUrl;
    public static Connection connection;

    /**
     * Lấy thông tin kết nối cơ sở dữ liệu
     * @return
     * @throws FileNotFoundException
     * @throws IOException 
     */
    public static boolean LoadConnectionInfo() throws FileNotFoundException, IOException {
        sUsername = "sa";
        sPassword = "duong537";
        sUrl = "jdbc:sqlserver://PHAMDUONG-PC:1433;databaseName=NGANHANG;User ID=sa;Password=duong537";

        return true;
    }

    /**
     * Mở kết nối cơ sở dữ liệu
     *
     * @return Kết quả trả về thông báo việc kết nối thành công hay không?
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static boolean OpenConnection() throws ClassNotFoundException, SQLException {
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        connection = DriverManager.getConnection(sUrl, sUsername, sPassword);
        return true;
    }

    /**
     * Đóng kết nối cơ sở dữ liệu
     *
     * @return Kết quả trả về thông báo việc đóng kết nối thành công hay không?
     * @throws SQLException
     */
    public static boolean CloseConnection() throws SQLException {
        if (connection != null) {
            connection.close();
        }
        return true;
    }

    /**
     * Lấy số lượng tham số của 1 store procedure
     *
     * @param con Connetion đang làm việc
     * @param procName Tên store procedure muốn thực thi
     * @return Số lượng tham số của store procedure muốn thực thi
     * @throws SQLException
     */
    private static int GetNumOfParasOfProc(Connection con, String procName) throws SQLException {
        DatabaseMetaData dbMetaData = con.getMetaData();
        ResultSet rs = dbMetaData.getProcedureColumns(con.getCatalog(),
                null,
                procName.toUpperCase(),
                "%");
        int i = 0;
        while (rs.next()) {
            i++;
        }
        return i;
    }

    /**
     * Lấy định dạng của store procedure dựa trên só tham số của nó
     *
     * @param num Số lượng tham số của store procedure
     * @return Định dạng của store procedure dựa trên só tham số của nó
     */
    public static String GetStoreProcedureFormat(int num) {
        String s = "";
        for (int i = 0; i < num; i++) {
            s += "?,";
        }
        if (s.endsWith(",")) {
            s = s.substring(0, s.length() - 1);
        }
        return s;
    }

    /**
     * Thực hiện gọi các truy vấn select
     *
     * @param procName Tên procedure thực hiện truy vấn
     * @param sqlParams Danh sách các tham số của store procedure
     * @return Trả về bảng chứa kết quả trả về từ csdl
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public static DefaultTableModel ExecuteQueryReturnTable(String procName,
            List<Parameter> sqlParams)
            throws SQLException, ClassNotFoundException {
        OpenConnection();
        int n = GetNumOfParasOfProc(connection, procName);
        String sCall = "{call " + procName + "(" + GetStoreProcedureFormat(n) + ")}";
        CallableStatement cs = connection.prepareCall(sCall);
        for (int i = 0; i < n; i++) {
            cs.setObject(sqlParams.get(i).Name, sqlParams.get(i).Value);
        }

        ResultSet rs = cs.executeQuery();
        ResultSetMetaData rsMeta = rs.getMetaData();
        int totalColumn = rsMeta.getColumnCount();
        Object[] dataRow = new Object[totalColumn];
        DefaultTableModel table = new DefaultTableModel();

        if (rs != null) {
            for (int i = 1; i <= totalColumn; i++) {
                table.addColumn(rsMeta.getColumnName(i));
            }
            while (rs.next()) {
                for (int i = 1; i <= totalColumn; i++) {
                    dataRow[i - 1] = rs.getObject(i);
                }
                table.addRow(dataRow);
            }
        }
        rs.close();
        cs.close();
        CloseConnection();
        return table;
    }

    public static ArrayList ExecuteQuery(String procName, List<Parameter> sqlParams)
            throws SQLException, ClassNotFoundException {
        OpenConnection();
        int n = GetNumOfParasOfProc(connection, procName);
        String sCall = "{call " + procName + "(" + GetStoreProcedureFormat(n) + ")}";
        CallableStatement cs = connection.prepareCall(sCall);
        for (int i = 0; i < n; i++) {
            cs.setObject(sqlParams.get(i).Name, sqlParams.get(i).Value);
        }

        ResultSet rs = cs.executeQuery();
        ResultSetMetaData rsMeta = rs.getMetaData();
        int totalColumn = rsMeta.getColumnCount();
    
//        if (rs != null) {
//            for (int i = 1; i <= totalColumn; i++) {
//                table.addColumn(rsMeta.getColumnName(i));
//            }
//            while (rs.next()) {
//                for (int i = 1; i <= totalColumn; i++) {
//                    dataRow[i - 1] = rs.getObject(i);
//                }
//                table.addRow(dataRow);
//            }
//        }
        ArrayList arr = new ArrayList();
        
        if (rs != null) {
            while (rs.next()) {
                ArrayList item = new ArrayList();
                for (int i = 1; i <= totalColumn; i++) {
                    //dataRow[i - 1] = rs.getObject(i);
                    item.add(rs.getObject(i));
                }
                arr.add(item);
                
            }
        }
        rs.close();
        cs.close();
        CloseConnection();
        return arr;
    }

    /**
     * Thực hiện truy vấn Update, Insert, Delete
     *
     * @param procName Tên store procedure thực thi
     * @param sqlParams Danh sách tham số của store procedure
     * @return Số lượng record bị tác động
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static int ExecuteNonQuery(String procName, List<Parameter> sqlParams)
            throws ClassNotFoundException, SQLException {
        OpenConnection();
        int n = GetNumOfParasOfProc(connection, procName);
        String sCall = "{call " + procName + " (" + GetStoreProcedureFormat(n) + ")}";
        CallableStatement cs = connection.prepareCall(sCall);
        for (int i = 0; i < n; i++) {
            cs.setObject(sqlParams.get(i).Name, sqlParams.get(i).Value);
        }

        int result = cs.executeUpdate();
        cs.close();
        CloseConnection();
        return result;
    }
}
